package com.lanyou.cook.web;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.Collator;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Comparator;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Tuple;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Join;
import javax.persistence.criteria.Path;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import org.apache.commons.beanutils.BeanMap;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.shiro.authz.UnauthenticatedException;
import org.apache.shiro.authz.annotation.Logical;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.pentaho.di.core.encryption.Encr;
import org.pentaho.di.core.encryption.KettleTwoWayPasswordEncoder;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import com.google.common.primitives.Ints;
import com.google.gson.JsonArray;
import com.google.gson.JsonObject;
import com.lanyou.cook.common.Constants;
import com.lanyou.cook.datadictionary.UserLogOpt;
import com.lanyou.cook.entity.domain.DBlinkInterface;
import com.lanyou.cook.entity.domain.Field;
import com.lanyou.cook.entity.domain.FieldSystem;
import com.lanyou.cook.entity.domain.KettleDataSourceEx;
import com.lanyou.cook.entity.domain.userManagement.User;
import com.lanyou.cook.entity.kettle.KettleDataSource;
import com.lanyou.cook.entity.kettle.KettleDatabaseType;
import com.lanyou.cook.entitylistener.domain.UserEntityListenerForKettleObject;
import com.lanyou.cook.repository.FieldSystemRepository;
import com.lanyou.cook.repository.KettleDataBaseTypeRepository;
import com.lanyou.cook.repository.KettleDataSourceExRepository;
import com.lanyou.cook.repository.KettleDataSourceRepository;
import com.lanyou.cook.repository.kettle.KettleJobDatabaseRepository;
import com.lanyou.cook.repository.kettle.KettleStepDatabaseRepository;
import com.lanyou.cook.service.UserLogService;
import com.lanyou.cook.service.param.ConnectOracleForImportParam;
import com.lanyou.cook.service.security.UserSessionService;
import com.lanyou.cook.service.vo.DBLinkParseResult;
import com.lanyou.cook.util.RSAUtil;
import com.lanyou.cook.web.param.KettleDataSourceExParam;
import com.lanyou.cook.web.param.KettleDataSourceParam;

/**
 * 领域数据源相关业务控制层
 * 
 * @author 仇伟业
 *
 */
@RestController
@RequestMapping(Constants.BASE_URL_API + "/kettleDataSourceExes")
public class KettleDataSourceExController {

	@PersistenceContext
	private EntityManager entityManager;
	@Autowired
	private KettleDataSourceExRepository kettleDataSourceExRepository;
	@Autowired
	private KettleDataSourceRepository kettleDataSourceRepository;
	@Autowired
	private FieldSystemRepository fieldSystemRepository;
	@Autowired
	private KettleDataBaseTypeRepository kettleDataBaseTypeRepository;
	@Autowired
	private UserSessionService userSessionService;
	@Autowired
	private KettleJobDatabaseRepository kettleJobDatabaseRepository;
	@Autowired
	private KettleStepDatabaseRepository kettleStepDatabaseRepository;

	@Autowired
	private UserLogService userLogService;

	/**
	 * 查询特定条件下的领域数据源集合
	 * 
	 * @param param
	 *            类型:KettleDataSourceParam 待查询的领域数据源参数实体
	 * @param pageable
	 *            类型:Pageable 待查询的分页参数实体
	 * @return
	 */
	@RequestMapping("/search/findByExample")
	@RequiresPermissions(value = { "fieldSystemSourceManagement:view",
			"fieldSystemSourceManagement:maintain" }, logical = Logical.OR)
	public Page<KettleDataSourceEx> findByExample(KettleDataSourceParam param, Pageable pageable) {
		Specification<KettleDataSourceEx> spec = new Specification<KettleDataSourceEx>() {
			@Override
			public Predicate toPredicate(Root<KettleDataSourceEx> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
				List<Predicate> predicatesList = new ArrayList<>();

				// 数据源名称
				if (StringUtils.isNotBlank(param.getName())) {
					predicatesList.add(cb.like(root.get("kettleDataSource").get("name"), '%' + param.getName() + '%'));
				}

				// 领域系统
				Path<String> pathOwnerFieldSystemId = root.get("fieldSystem").get("id");
				if (StringUtils.isNotBlank(param.getFieldSystemId())) {
					predicatesList.add(cb.equal(pathOwnerFieldSystemId, param.getFieldSystemId()));
				} else {
					// 默认查当前登录用户所管理的领域系统
					Collection<String> fieldSystemIds = userSessionService
							.findFieldSystemIdsOfCurrentAuthenticatedUserCanManage();
					if (!CollectionUtils.isEmpty(fieldSystemIds)) {
						predicatesList.add(pathOwnerFieldSystemId.in(fieldSystemIds));
					} else {
						predicatesList.add(cb.notEqual(cb.literal(1), 1));
					}
				}

				// 领域
				if (StringUtils.isNotBlank(param.getFieldId())) {
					predicatesList.add(cb.equal(root.get("fieldSystem").get("field").get("id"), param.getFieldId()));
				}

				// 数据库类型
				if (StringUtils.isNotBlank(param.getDataBaseTypeCode())) {
					predicatesList.add(cb.equal(root.get("kettleDataSource").get("dataBaseType").get("code"),
							param.getDataBaseTypeCode()));
				}

				// 主机
				if (StringUtils.isNotBlank(param.getHostName())) {
					predicatesList.add(
							cb.like(root.get("kettleDataSource").get("hostName"), '%' + param.getHostName() + '%'));
				}

				// 数据库名称
				if (StringUtils.isNotBlank(param.getDatabaseName())) {
					predicatesList.add(cb.like(root.get("kettleDataSource").get("databaseName"),
							'%' + param.getDatabaseName() + '%'));
				}
				// 是否为集群
				if (StringUtils.isNotBlank(param.getIsCluster())) {
					predicatesList.add(cb.like(root.get("isCluster"), param.getIsCluster()));
				}

				query.where(predicatesList.toArray(new Predicate[predicatesList.size()]));
				return null;
			}
		};

		return kettleDataSourceExRepository.findAll(spec, pageable);
	}

	/**
	 * 新增或修改领域数据源
	 * 
	 * @param param
	 *            类型:KettleDataSourceParam 待新增或修改的领域数据源参数实体
	 */
	@RequestMapping(value = "/savaOrUpdate", produces = "application/json", method = RequestMethod.POST)
	@RequiresPermissions(value = { "fieldSystemSourceManagement:maintain" })
	public String savaOrUpdate(@RequestBody KettleDataSourceParam param) {
		boolean flag = false;
		KettleDataSourceEx kettleDataSourceEx = kettleDataSourceExRepository
				.findOne(KettleDataSourceEx.idSpecification(param.getKettleDataSourceExId()));
		if (kettleDataSourceEx == null) {
			kettleDataSourceEx = new KettleDataSourceEx();
			flag = true;
		}

		KettleDataSource kettleDataSource = kettleDataSourceEx.getKettleDataSource();
		if (kettleDataSource == null) {
			kettleDataSource = new KettleDataSource();
			kettleDataSourceEx.setKettleDataSource(kettleDataSource);
		}

		JsonObject result = new JsonObject();
		KettleDataSourceEx kettleDataSourceExcheck = kettleDataSourceExRepository
				.findByKettleDataSource_Name(param.getName());
		if (kettleDataSourceExcheck != null) {
			if (!StringUtils.equals(kettleDataSourceExcheck.getId(), param.getKettleDataSourceExId())) {
				result.addProperty("isCanSave", false);
				result.addProperty("errorCode", "DUPLICATE");
				return result.toString();
			}
		}
		// 运用BeanMap拷贝属性
		String password = "";
		try {
			// 进行RSA解密
			password = RSAUtil.decryptByPrivateKey(param.getPassword(), Constants.PRIVATE_KEY_BASE64);
			param.setPassword(password);
		} catch (Exception e) {

		}

		if (StringUtils.isEmpty(password))
			throw new RuntimeException("Password is invalid");

		// 原密码未经过加密，则此时加密
		if (!password.startsWith(KettleTwoWayPasswordEncoder.PASSWORD_ENCRYPTED_PREFIX)) {
			String encryptedPassword = Encr.encryptPasswordIfNotUsingVariables(password);
			kettleDataSource.setPassword(encryptedPassword);
			param.setPassword(encryptedPassword);
		}

		BeanMap beanMapFromParam = new BeanMap(param);
		BeanMap beanMapFromDB = new BeanMap(kettleDataSource);
		final String[] propertyNames = { "name", "hostName", "port", "databaseName", "username", "password" };
		Arrays.stream(propertyNames).forEach(p -> beanMapFromDB.put(p, beanMapFromParam.get(p)));

		// 设置数据库类型
		KettleDatabaseType kettleDatabaseType = kettleDataBaseTypeRepository.findByCode(param.getDataBaseTypeCode());
		kettleDataSource.setDataBaseType(kettleDatabaseType);

		// 设置所属领域系统
		FieldSystem fieldSystem = fieldSystemRepository.findOne(FieldSystem.idSpecification(param.getFieldSystemId()));
		kettleDataSourceEx.setFieldSystem(fieldSystem);

		// 设置备注
		kettleDataSourceEx.setRemark(param.getRemark());

		// 设置是否为集群
		kettleDataSourceEx.setIsCluster(param.getIsCluster());

		kettleDataSourceExRepository.save(Arrays.asList(kettleDataSourceEx));

		result.addProperty("errorCode", "SUCCESS");
		String userCode = UserSessionService.getCurrentAuthenticatedUserCode();
		if (flag) {
			userLogService.save(UserLogOpt.MaintenanceFieldSystemDataSource, null,
					String.format("新增了领域系统数据源“%s”的信息", kettleDataSourceEx.getKettleDataSource().getName()));
		} else {
			userLogService.save(UserLogOpt.MaintenanceFieldSystemDataSource, null,
					String.format("修改了领域系统数据源“%s”的信息", kettleDataSourceEx.getKettleDataSource().getName()));
		}
		return result.toString();
	}

	// @Component
	@RequestMapping(value = "/search/checkIsCanDelete", produces = "application/json")
	public String checkIsCanDelete(String id) {
		KettleDataSourceEx kettleDataSource = kettleDataSourceExRepository.findOne(id);
		long dataId = kettleDataSource.getKettleDataSource().getId();
		// 先把long转换成字符串String，然后在转行成Integer
		int databaseId = Integer.parseInt(String.valueOf(dataId));

		int kettleJobDatabase = kettleJobDatabaseRepository.findByDatabaseId(databaseId);
		int kettleStepDatabase = kettleStepDatabaseRepository.findByDatabaseId(databaseId);

		if (kettleJobDatabase == 0 || kettleStepDatabase == 0) {
			JsonObject result = new JsonObject();
			result.addProperty("isCanDelete", true);
			return result.toString();
		} else {
			JsonObject result = new JsonObject();
			result.addProperty("isCanDelete", false);
			result.addProperty("reason", "该领域数据源被引用，不能删除");
			return result.toString();
		}
	}

	// 测试数据库连接
	@RequestMapping(value = "/link/dataBase", method = RequestMethod.GET)
	public String linkDatBase(KettleDataSourceExParam param) {

		String result = "对不起,暂时不支持" + param.getDataBase() + "测试连接";
		String url = "";
		String driver = "";
		Connection con = null;// @创建一个数据库连接

		// oracle
		if (StringUtils.equalsIgnoreCase(param.getDataBase(), "Oracle")) {
			result = "";
			driver = "oracle.jdbc.driver.OracleDriver";// 驱动
			if (StringUtils.equals(param.getIsCluster(), "2")) {
				String dataSourceName = "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =" + param.getHost()
						+ ")(PORT = " + param.getPort() + "))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = "
						+ param.getDataSourceName()
						+ ")(FAILOVER_MODE =(TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5))))";
				url = String.format("jdbc:oracle:thin:@%s", dataSourceName);
			} else {
				url = String.format("jdbc:oracle:thin:@%s:%d:%s", param.getHost(), param.getPort(),
						param.getDataSourceName());
			}
		} else if (StringUtils.equalsIgnoreCase(param.getDataBase(), "MySQL")) {
			result = "";
			driver = "com.mysql.jdbc.Driver";
			url = String.format("jdbc:mysql://%s:%d/%s", param.getHost(), param.getPort(), param.getDataSourceName());

		} else if (StringUtils.equalsIgnoreCase(param.getDataBase(), "MS SQL Server")) {
			result = "";
			driver = "net.sourceforge.jtds.jdbc.Driver";
			url = String.format("jdbc:jtds:sqlserver://%s:%d/%s", param.getHost(), param.getPort(),
					param.getDataSourceName());
		}

		String userName = param.getUserCode();// 用户名
		String passWord = param.getPassword();
		try {
			// 进行RSA解密
			passWord = RSAUtil.decryptByPrivateKey(param.getPassword(), Constants.PRIVATE_KEY_BASE64);
		} catch (Exception e) {

		}
		if (passWord.startsWith(KettleTwoWayPasswordEncoder.PASSWORD_ENCRYPTED_PREFIX)) {
			passWord = Encr.decryptPassword(passWord);
		}
		try {
			if (!StringUtils.equalsIgnoreCase(result, "对不起,暂时不支持" + param.getDataBase() + "测试连接")) {
				Class.forName(driver);
				con = DriverManager.getConnection(url, userName, passWord);
				System.out.println(con);
				result = "测试连接成功！";
			}
			return result;
		} catch (Exception e) {
			e.printStackTrace();
			return e.getMessage();
		}

		finally {
			try {
				// 逐一将上面的几个对象关闭，因为不关闭的话会影响性能、并且占用资源
				// 注意关闭的顺序，最后使用的最先关闭
				if (con != null)
					con.close();
				System.out.println("数据库连接已关闭！");
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	// 解析
	@RequestMapping(value = "/analyze", method = RequestMethod.GET)
	public DBLinkParseResult analyticalTns(String dataSourceName) {

		Pattern pattern_host = Pattern.compile("(\\w*)=.*?\\).*?\\(HOST=(.*?)\\)\\(");
		Pattern pattern_port = Pattern.compile("(\\w*)=.*?\\).*?\\(PORT=(.*?)\\)\\)\\(");// service_name方式的port匹配
		Pattern pattern_port_sid = Pattern.compile("(\\w*)=.*?\\).*?\\(PORT=(.*?)\\)\\)\\("); // SID方式的port匹配
		Pattern pattern_servicename = Pattern.compile("(\\w*)=.*?\\).*?\\(SERVICE_NAME=(.*?)\\)\\(");
		Pattern pattern_sid = Pattern.compile("(\\w*)=.*?\\).*?\\(SID=(.*?)\\)\\)\\)");

		Matcher matcher_host = pattern_host.matcher(dataSourceName);
		Matcher matcher_port = pattern_port.matcher(dataSourceName);
		Matcher matcher_port_sid = pattern_port_sid.matcher(dataSourceName);
		Matcher matcher_servicename = pattern_servicename.matcher(dataSourceName);
		Matcher matcher_sid = pattern_sid.matcher(dataSourceName);

		String str_host = "";
		String str_port = "";
		String str_servicename = "";
		String str_sid = "";

		while (matcher_host.find()) {
			str_host = matcher_host.group(2);
		}

		while (matcher_servicename.find()) {
			str_servicename = matcher_servicename.group(2);
		}
		while (matcher_sid.find()) {
			str_sid = matcher_sid.group(2);
		}

		// sid不为空，表示采用SID的正则表达式来匹配端口
		if (str_sid != "") {
			while (matcher_port_sid.find()) {
				str_port = matcher_port_sid.group(2);
			}
		} else {
			while (matcher_port.find()) {
				str_port = matcher_port.group(2);
			}
		}

		Integer port = Ints.tryParse(str_port);
		if (str_host == "" && str_port == "" && str_servicename == "" && str_sid == "") {
			DBLinkParseResult result = null;
			result = new DBLinkParseResult();
			result.setType(DBLinkParseResult.TYPE_NULL);
			return result;
		} else {
			if (str_host == "" || str_port == "") {
				return null;
			} else {
				if (str_servicename != "") {
					DBLinkParseResult result = null;
					result = new DBLinkParseResult();
					result.setType(DBLinkParseResult.TYPE_SERVICE_NAME);
					result.setHost(str_host);
					result.setPort(port);
					result.setServiceName(str_servicename);
					return result;
				} else {
					DBLinkParseResult result = null;
					result = new DBLinkParseResult();
					result.setHost(str_host);
					result.setPort(port);
					result.setType(DBLinkParseResult.TYPE_SID);
					result.setSID(str_sid);
					return result;
				}
			}
		}
	}

	/**
	 * 获取所有的领域系统数据源
	 * 
	 * @return 类型:List<KettleDataSourceEx> 返回一个领域系统数据源集合
	 */
	@RequestMapping("/search/findAllKettleDataSource")
	public List<KettleDataSourceEx> findAllKettleDataSource() {
		Specification<KettleDataSourceEx> spec = new Specification<KettleDataSourceEx>() {
			@Override
			public Predicate toPredicate(Root<KettleDataSourceEx> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
				List<Predicate> predicatesList = new ArrayList<>();

				// 领域系统
				Path<String> pathOwnerFieldSystemId = root.get("fieldSystem").get("id");
				// 默认查当前登录用户所管理的领域系统
				Collection<String> fieldSystemIds = userSessionService
						.findFieldSystemIdsOfCurrentAuthenticatedUserCanManage();
				if (!CollectionUtils.isEmpty(fieldSystemIds)) {
					predicatesList.add(pathOwnerFieldSystemId.in(fieldSystemIds));
				} else {
					predicatesList.add(cb.notEqual(cb.literal(1), 1));
				}
				query.where(predicatesList.toArray(new Predicate[predicatesList.size()]));
				return null;
			}
		};

		return kettleDataSourceExRepository.findAll(spec);

	}

}
